package com.lanvideo.dao;

import com.lanvideo.entity.PlaylistVideo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

/**
 * 播放列表视频关联数据访问层
 */
@Repository
public class PlaylistVideoDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加视频到播放列表
     */
    public int addVideoToPlaylist(Integer playlistId, Integer videoId) {
        // 获取当前播放列表中的最大顺序号
        String maxOrderSql = "SELECT COALESCE(MAX(order_num), 0) FROM playlist_video WHERE playlist_id = ?";
        Integer maxOrder = jdbcTemplate.queryForObject(maxOrderSql, Integer.class, playlistId);
        int nextOrder = (maxOrder != null ? maxOrder : 0) + 1;
        
        String sql = "INSERT INTO playlist_video (playlist_id, video_id, order_num) VALUES (?, ?, ?)";
        return jdbcTemplate.update(sql, playlistId, videoId, nextOrder);
    }

    /**
     * 从播放列表移除视频
     */
    public int removeVideoFromPlaylist(Integer playlistId, Integer videoId) {
        String sql = "DELETE FROM playlist_video WHERE playlist_id = ? AND video_id = ?";
        return jdbcTemplate.update(sql, playlistId, videoId);
    }

    /**
     * 更新视频在播放列表中的顺序
     */
    public int updateVideoOrder(Integer playlistId, Integer videoId, Integer orderNum) {
        String sql = "UPDATE playlist_video SET order_num = ? WHERE playlist_id = ? AND video_id = ?";
        return jdbcTemplate.update(sql, orderNum, playlistId, videoId);
    }

    /**
     * 获取播放列表中的所有视频
     */
    public List<PlaylistVideo> getPlaylistVideos(Integer playlistId) {
        String sql = "SELECT pv.*, p.name as playlistName, v.title as videoTitle, v.intro as videoIntro, " +
                "v.tags as videoTags, v.upload_time as uploadTime, v.view_count as viewCount, u.name as uploaderName " +
                "FROM playlist_video pv " +
                "LEFT JOIN playlist p ON pv.playlist_id = p.id " +
                "LEFT JOIN video v ON pv.video_id = v.id " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE pv.playlist_id = ? " +
                "ORDER BY pv.order_num ASC, pv.add_time ASC";
        return jdbcTemplate.query(sql, new PlaylistVideoRowMapper(), playlistId);
    }

    /**
     * 检查视频是否在播放列表中
     */
    public boolean isVideoInPlaylist(Integer playlistId, Integer videoId) {
        String sql = "SELECT COUNT(*) FROM playlist_video WHERE playlist_id = ? AND video_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, playlistId, videoId);
        return count != null && count > 0;
    }

    /**
     * 获取视频在播放列表中的信息
     */
    public PlaylistVideo getPlaylistVideo(Integer playlistId, Integer videoId) {
        String sql = "SELECT pv.*, p.name as playlistName, v.title as videoTitle, v.intro as videoIntro, " +
                "v.tags as videoTags, v.upload_time as uploadTime, v.view_count as viewCount, u.name as uploaderName " +
                "FROM playlist_video pv " +
                "LEFT JOIN playlist p ON pv.playlist_id = p.id " +
                "LEFT JOIN video v ON pv.video_id = v.id " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE pv.playlist_id = ? AND pv.video_id = ?";
        List<PlaylistVideo> videos = jdbcTemplate.query(sql, new PlaylistVideoRowMapper(), playlistId, videoId);
        return videos.isEmpty() ? null : videos.get(0);
    }

    /**
     * 获取播放列表中的视频数量
     */
    public int getPlaylistVideoCount(Integer playlistId) {
        String sql = "SELECT COUNT(*) FROM playlist_video WHERE playlist_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, playlistId);
        return count != null ? count : 0;
    }

    /**
     * 清空播放列表中的所有视频
     */
    public int clearPlaylist(Integer playlistId) {
        String sql = "DELETE FROM playlist_video WHERE playlist_id = ?";
        return jdbcTemplate.update(sql, playlistId);
    }

    /**
     * 获取用户的所有播放列表视频
     */
    public List<PlaylistVideo> getUserPlaylistVideos(Integer userId) {
        String sql = "SELECT pv.*, p.name as playlistName, v.title as videoTitle, v.intro as videoIntro, " +
                "v.tags as videoTags, v.upload_time as uploadTime, v.view_count as viewCount, u.name as uploaderName " +
                "FROM playlist_video pv " +
                "LEFT JOIN playlist p ON pv.playlist_id = p.id " +
                "LEFT JOIN video v ON pv.video_id = v.id " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE p.user_id = ? " +
                "ORDER BY pv.add_time DESC";
        return jdbcTemplate.query(sql, new PlaylistVideoRowMapper(), userId);
    }

    /**
     * 自定义RowMapper用于映射PlaylistVideo对象
     */
    private static class PlaylistVideoRowMapper implements RowMapper<PlaylistVideo> {
        @Override
        public PlaylistVideo mapRow(@NonNull ResultSet rs, int rowNum) throws SQLException {
            PlaylistVideo playlistVideo = new PlaylistVideo();
            
            // 基本字段
            playlistVideo.setId(rs.getInt("id"));
            playlistVideo.setPlaylistId(rs.getInt("playlist_id"));
            playlistVideo.setVideoId(rs.getInt("video_id"));
            playlistVideo.setOrderNum(rs.getInt("order_num"));
            
            // 时间字段处理
            Timestamp addedTime = rs.getTimestamp("add_time");
            if (addedTime != null) {
                playlistVideo.setAddedTime(addedTime.toLocalDateTime());
            }
            
            // 关联字段
            playlistVideo.setPlaylistName(rs.getString("playlistName"));
            playlistVideo.setVideoTitle(rs.getString("videoTitle"));
            playlistVideo.setVideoIntro(rs.getString("videoIntro"));
            playlistVideo.setVideoTags(rs.getString("videoTags"));
            playlistVideo.setUploaderName(rs.getString("uploaderName"));
            
            // 视频时间字段处理
            Timestamp uploadTime = rs.getTimestamp("uploadTime");
            if (uploadTime != null) {
                playlistVideo.setUploadTime(uploadTime.toLocalDateTime());
            }
            
            playlistVideo.setViewCount(rs.getInt("viewCount"));
            
            return playlistVideo;
        }
    }
}
